﻿using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using NTQOnlineExam.Services.Infrastructure;

namespace NTQOnlineExam.Services.ReportService.Term
{
  public class GetTermYearListDbCommand : ICommand<IList<string>>
  {
    private const string SQL = @"
 Select DISTINCT SUBSTRING(Termname, 0,  CHARINDEX('-', TermName)) AS TermYear from dbo.[Term]
    ";


    public IList<string> Execute()
    {
      var connectionString = ConfigurationManager.AppSettings["ConnectionString"];

      using (SqlConnection connection = new SqlConnection(connectionString))
      {
        IList<string> items = new List<string>();
        SqlCommand cmd = new SqlCommand(SQL, connection);
        connection.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        try
        {
          while (reader.Read())
          {
            items.Add(reader.GetString(0));
          }
        }
        finally
        {
          reader.Close();
        }
        return items;
      }
    }
  }
}

